package com.cn.wanxi.dao.impl;

import com.cn.wanxi.dao.UserDao;
import com.cn.wanxi.model.UserModel;
import com.cn.wanxi.util.JDBC;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserImpl implements UserDao {
    @Override
    public UserModel findByUsernameAndPassword(UserModel userModel) {
        String sql = "SELECT * FROM user where username='" + userModel.getUsername() + "' and password ='" + userModel.getPassword() + "';";
//        需要连接数据库
        ResultSet rs = JDBC.query(sql);
        UserModel model = null;
        try {
            while (rs.next()) {
                model = new UserModel();
//                model.setUsername(rs.getString("username"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return model;
    }

    @Override
    public List<UserModel> findAll(UserModel userModel) {
        String sql = "select *,date_format(birthday ,'%Y-%m-%d') temp from user order by id desc ";
        sql += " limit " + (userModel.getPage() - 1) * userModel.getLimit() + "," + userModel.getLimit() + "";
        return getList(sql);
    }

    private List<UserModel> getList(String sql) {
        ResultSet resultSet = JDBC.query(sql);
        List<UserModel> list = new ArrayList<>();
        try {
            while (resultSet.next()) {
                UserModel model = new UserModel();
                model.setId(resultSet.getInt("id"));
                model.setUsername(resultSet.getString("username"));
                model.setPhone(resultSet.getString("phone"));
                model.setSex(resultSet.getString("sex"));
                model.setBirthday(resultSet.getString("temp"));
                model.setEmail(resultSet.getString("email"));
                model.setCreateTime(resultSet.getString("create_time"));
                model.setUpdateTime(resultSet.getString("update_time"));
                model.setEnable(resultSet.getInt("enable"));
                model.setHobby(resultSet.getString("hobby"));
                model.setAddress(resultSet.getString("address"));
                model.setRemark(resultSet.getString("remark"));
                list.add(model);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 如果我们对username设置了unique，唯一，那么返回的是影响的条数为0
     *
     * @param userModel
     * @return
     */
    @Override
    public Integer add(UserModel userModel) {
        String sql = "INSERT INTO `user` (" +
                "`username`, `address`, `create_time`," +
                " `email`, `enable`, `password`, " +
                "`phone`, `sex`, `update_time`," +
                " `hobby`, `birthday`, `remark`) VALUES (" +
                "'" + userModel.getUsername() + "', '" + userModel.getAddress() + "', now()," +
                " '" + userModel.getEmail() + "', '" + userModel.getEnable() + "', '" + userModel.getPassword() + "'," +
                " '" + userModel.getPhone() + "', '" + userModel.getSex() + "', now()," +
                " '" + userModel.getHobby() + "', '" + userModel.getBirthday() + "', '" + userModel.getRemark() + "');";

        return JDBC.update(sql);
    }

    @Override
    public Integer getCount(UserModel userModel) {
        String sql = "select count(*) count from user";
        ResultSet resultSet = JDBC.query(sql);
        try {
            while (resultSet.next()) {
                return resultSet.getInt("count");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public UserModel findById(UserModel userModel) {
        String sql = "select *,date_format(birthday ,'%Y-%m-%d') temp from user where id = " + userModel.getId();
//        ResultSet resultSet = JDBC.query(sql);
        List<UserModel> list = getList(sql);
        return list.size() > 0 ? list.get(0) : null;
//        return getList(sql).size() > 0 ? getList(sql).get(0) : null;
    }

    @Override
    public Integer update(UserModel userModel) {
        String sql = "" +
                "UPDATE `user` SET " +
                "`username` = '" + userModel.getUsername() + "', " +
                "`address` = '" + userModel.getAddress() + "', " +

                "`email` = '" + userModel.getEmail() + "'," +
                " `enable` = '" + userModel.getEnable() + "', " +

                " `phone` = '" + userModel.getPhone() + "', " +
                " `sex` = '" + userModel.getSex() + "', " +
                " `update_time` = now(), " +
                " `hobby` = '" + userModel.getHobby() + "', " +
                " `birthday` = '" + userModel.getBirthday() + "'," +
                " `remark` = '" + userModel.getRemark() + "' " +
                " WHERE (`id` = '" + userModel.getId() + "');";
        return JDBC.update(sql);
    }

    @Override
    public Integer del(UserModel userModel) {
        String sql = "delete from user where id=" + userModel.getId();
        return JDBC.update(sql);
    }

    /**
     * 判断以前的enable是1，那么修改为0，如果是0，则修改为1
     * @param userModel
     * @return
     */
    @Override
    public Integer enable(UserModel userModel) {
//        String sql = "update user set enable^1 where id=" + userModel.getId();
//        String sql = "update user set abs(enable-1) where id=" + userModel.getId();
        String sql = "update user set enable = case when 1 then 0 when 0 then 1 end where id=" + userModel.getId();
        return JDBC.update(sql);
    }
}
